import pandas as pd
import json
import os

import requests

def extract_excel_to_json(path):
    result = {
        "paper_equip": [],
        "paper_cable": []
    }

    try:
        xls = pd.ExcelFile(path, engine='openpyxl')
        for sheet_name in xls.sheet_names:
            try:
                df = pd.read_excel(path, sheet_name=sheet_name, engine='openpyxl').fillna('')
                columns = df.columns.astype(str)

                # ---------------- 设备清单 ----------------
                if '设备型号' in columns and '设备厂家' in columns:
                    for _, row in df.iterrows():
                        equip_data = {
                            "sequence_number": try_parse_int(row.get('序号')),
                            "design_name": str(row.get('名称', '')).strip(),
                            "design_count": try_parse_int(row.get('设计数量')),
                            "material_attribute": str(row.get('物资属性', '')).strip()
                        }
                        result["paper_equip"].append(equip_data)

                # ---------------- 电缆清单 ----------------
                elif '导线起点' in columns and '导线终点' in columns:
                    for _, row in df.iterrows():
                        cable_data = {
                            "sequence_number": try_parse_int(row.get('序号')),
                            "cable_function": str(row.get('功能名称', '')).strip(),
                            "design_name": str(row.get('名称', '')).strip(),
                            "wire_start": str(row.get('导线起点', '')).strip(),
                            "wire_end": str(row.get('导线终点', '')).strip(),
                            "design_count": try_parse_int(row.get('设计条数')),
                            "cable_length": try_parse_float(row.get('线缆长度（米/条）')),
                            "material_attribute": str(row.get('物资属性', '')).strip()
                        }
                        result["paper_cable"].append(cable_data)

            except Exception as e:
                print(f"❌ 无法读取 sheet: {sheet_name} in {path}, 错误: {e}")
    except Exception as e:
        print(f"❌ 无法读取 Excel 文件: {path}, 错误: {e}")

    return result


def try_parse_int(value):
    try:
        return int(float(value))
    except:
        return 0


def try_parse_float(value):
    try:
        return round(float(value), 2)
    except:
        return 0.0

def give_back_json_path(excel_paths):
    json_paths = []
    output_dir = "output_json" 
    os.makedirs(output_dir, exist_ok=True)

    for path in excel_paths:
        json_data = extract_excel_to_json(path)
        filename = os.path.splitext(os.path.basename(path))[0] + ".json"
        output_path = os.path.join(output_dir, filename)

        with open(output_path, "w", encoding="utf-8") as f:
            json.dump(json_data, f, indent=2, ensure_ascii=False)
        json_paths.append(output_path)
        print(f"✅ 已保存: {output_path}")
    return json_paths

def submit_json_for_review(json_paths, api_url):
    results = []
    headers = {'Content-Type': 'application/json'}

    for json_file in json_paths:
        try:
            with open(json_file, 'r', encoding='utf-8') as f:
                data = json.load(f)
            
            # POST 请求发送 JSON 数据
            response = requests.post(api_url, json=data, headers=headers)

            if response.status_code == 200:
                result = response.json()
                print(f"✅ 审阅成功: {json_file}")
            else:
                result = {
                    "file": json_file,
                    "error": f"接口返回状态码 {response.status_code}",
                    "response_text": response.text
                }
                print(f"⚠️ 审阅失败: {json_file}")

        except Exception as e:
            result = {
                "file": json_file,
                "error": str(e)
            }
            print(f"❌ 处理异常: {json_file}，错误: {e}")
        
        results.append(result)

    return results
if __name__ == "__main__":
    excel_paths = [
        r"E:\cadsecond-processing\retrieve_folder\海宁长安皮毛市场汇聚机房近端机房设备平面布置图_3_20250718_084640_063_tables.xlsx",
        r"E:\cadsecond-processing\retrieve_folder\海宁长安皮毛市场汇聚机房线缆路由及GPS天线安装示意图_4_20250718_084643_583_tables.xlsx",
        r'E:\cadsecond-processing\retrieve_folder\远端机房设备平面布置图_5_20250718_084647_061_tables.xlsx',
        r'E:\cadsecond-processing\retrieve_folder\远端机房线缆路由图_6_20250718_084650_390_tables.xlsx',
        r'E:\cadsecond-processing\retrieve_folder\远端基站天馈安装示意图_7_20250718_084653_992_tables.xlsx'
    ]
    json_paths = give_back_json_path(excel_paths)

    # 替换为你实际的接口地址
    review_api_url = "http://127.0.0.1:8000/audit"
    
    review_results = submit_json_for_review(json_paths, review_api_url)
    print("📋 审阅接口返回结果：")
    print(json.dumps(review_results, indent=2, ensure_ascii=False))